import com.alibaba.excel.util.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;


import java.io.File;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 这个类只支持oracle，因为SQL只针对oracle来写了
 *  如果需要其他数据库使用可以自己手动改一下，后续会完善支持mysql和sqlsever
 */
public class TableInfoToExcel {

    //下面是填写你的链接信息 替换 【】 以及内的内容
    private static final String URL = "jdbc:oracle:thin:@【填写你的数据库IP】:1521:orcl";
    private static final String USER = "【填写你的数据库账号】";
    private static final String PASSWORD = "【填写你的数据密码】";

    public static void main(String[] args) {
        // 查询表信息
        Map<String, List<TableColumn>> tableMap = queryTableInfo();

        // 生成文件路径：保存到桌面
        String desktopPath = System.getProperty("user.home") + File.separator + "Desktop";
        String fileName = desktopPath + File.separator + "table_info.xlsx";

        // 导出到 Excel
        generateExcel(tableMap, fileName);

        System.out.println("Excel 文件已生成：" + fileName);
    }

    /**
     * 查询表信息，并按表名分组
     */
    private static Map<String, List<TableColumn>> queryTableInfo() {
        Map<String, List<TableColumn>> tableMap = new HashMap<>();
        String sql = """
                SELECT 
                    tc.table_name,              -- 表名
                    tc_comments.comments AS table_comment, -- 表注释
                    tc.column_name,             -- 列名
                    tc.data_type,               -- 数据类型
                    tc.data_length,             -- 数据长度
                    tc.nullable,                -- 是否允许为空
                    cc.comments AS column_comment, -- 列注释
                    CASE 
                        WHEN pkc.column_name IS NOT NULL THEN '是' 
                        ELSE '否' 
                    END AS is_primary_key,      -- 是否为主键
                    tc.data_precision,          -- 精度（仅适用于数值类型）
                    tc.data_scale               -- 小数位（仅适用于数值类型）
                FROM 
                    user_tab_columns tc         -- 表列信息
                LEFT JOIN 
                    user_col_comments cc        -- 列注释
                ON 
                    tc.table_name = cc.table_name AND tc.column_name = cc.column_name
                LEFT JOIN 
                    user_tab_comments tc_comments -- 表注释
                ON 
                    tc.table_name = tc_comments.table_name
                LEFT JOIN (
                    SELECT ucc.table_name, ucc.column_name
                    FROM user_constraints uc
                    JOIN user_cons_columns ucc
                    ON uc.constraint_name = ucc.constraint_name
                    WHERE uc.constraint_type = 'P' -- 主键约束
                ) pkc
                ON tc.table_name = pkc.table_name AND tc.column_name = pkc.column_name
                ORDER BY 
                    tc.table_name, tc.column_name
                """;

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                TableColumn column = new TableColumn();
                column.setTableName(getSafeString(rs, "table_name"));
                column.setTableComment(getSafeString(rs, "table_comment"));
                column.setColumnName(getSafeString(rs, "column_name"));
                column.setDataType(getSafeString(rs, "data_type"));
                column.setDataLength(rs.getInt("data_length")); // 默认值为 0
                column.setNullable("Y".equalsIgnoreCase(getSafeString(rs, "nullable")));
                column.setColumnComment(getSafeString(rs, "column_comment"));
                column.setPrimaryKey("是".equals(getSafeString(rs, "is_primary_key")));
                column.setDataPrecision(rs.getInt("data_precision")); // 默认值为 0
                column.setDataScale(rs.getInt("data_scale")); // 默认值为 0

                // 按表名分组
                tableMap.computeIfAbsent(column.getTableName(), k -> new ArrayList<>()).add(column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableMap;
    }

    /**
     * 安全地从 ResultSet 获取字符串值
     */
    private static String getSafeString(ResultSet rs, String columnName) throws SQLException {
        String value = rs.getString(columnName);
        return value != null ? value : "";
    }

    /**
     * 导出数据到 Excel，并优化格式
     */
    private static void generateExcel(Map<String, List<TableColumn>> tableMap, String fileName) {
        if (tableMap == null || tableMap.isEmpty()) {
            System.out.println("警告：没有数据可写入 Excel！");
            return;
        }

        // 使用 SXSSFWorkbook 来提高大文件的处理效率
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("表信息");

        // 创建样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle contentStyle = createContentStyle(workbook);
        CellStyle tableHeaderStyle = createTableHeaderStyle(workbook);

        int rowIndex = 0;

        for (Map.Entry<String, List<TableColumn>> entry : tableMap.entrySet()) {
            String tableName = entry.getKey();
            String tableComment = entry.getValue().get(0).getTableComment(); // 表注释
            List<TableColumn> columns = entry.getValue();

            // 添加表名和表注释
            Row tableRow = sheet.createRow(rowIndex++);
            Cell tableCell = tableRow.createCell(0);
            if(StringUtils.isEmpty(tableComment)){
                tableCell.setCellValue( tableName );
            }else {
                tableCell.setCellValue(tableName + " — " + tableComment);
            }
            tableCell.setCellStyle(headerStyle);

            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 7));

            // 添加列信息表头
            Row headerRow = sheet.createRow(rowIndex++);
            String[] headers = {"列名", "数据类型", "数据长度", "是否允许为空", "列注释", "是否为主键", "精度", "小数位"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(tableHeaderStyle);
            }

            // 写入列信息
            for (TableColumn column : columns) {
                Row row = sheet.createRow(rowIndex++);
                setCellValue(row, 0, column.getColumnName(), contentStyle);
                setCellValue(row, 1, column.getDataType(), contentStyle);
                setCellValue(row, 2, column.getDataLength(), contentStyle);
                setCellValue(row, 3, column.isNullable() ? "是" : "否", contentStyle);
                setCellValue(row, 4, column.getColumnComment(), contentStyle);
                setCellValue(row, 5, column.isPrimaryKey() ? "是" : "否", contentStyle);
                setCellValue(row, 6, column.getDataPrecision() > 0 ? column.getDataPrecision() : "", contentStyle);
                setCellValue(row, 7, column.getDataScale() > 0 ? column.getDataScale() : "", contentStyle);
            }

            // 空行分隔
            rowIndex++;
        }

        // 输出 Excel 文件
        try (FileOutputStream fileOut = new FileOutputStream(fileName)) {
            workbook.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ((SXSSFWorkbook) workbook).dispose(); // 清理临时文件
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 设置单元格值和样式
     */
    private static void setCellValue(Row row, int columnIndex, Object value, CellStyle style) {
        Cell cell = row.createCell(columnIndex);
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
    }

    /**
     * 创建表头样式
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 创建列信息表头样式
     */
    private static CellStyle createTableHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    /**
     * 创建内容样式
     */
    private static CellStyle createContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    /**
     * 表列信息实体类
     */
    static class TableColumn {
        private String tableName;
        private String tableComment;
        private String columnName;
        private String dataType;
        private int dataLength;
        private boolean nullable;
        private String columnComment;
        private boolean primaryKey;
        private int dataPrecision;
        private int dataScale;

        // Getters and Setters...
        public String getTableName() { return tableName; }
        public void setTableName(String tableName) { this.tableName = tableName; }
        public String getTableComment() { return tableComment; }
        public void setTableComment(String tableComment) { this.tableComment = tableComment; }
        public String getColumnName() { return columnName; }
        public void setColumnName(String columnName) { this.columnName = columnName; }
        public String getDataType() { return dataType; }
        public void setDataType(String dataType) { this.dataType = dataType; }
        public int getDataLength() { return dataLength; }
        public void setDataLength(int dataLength) { this.dataLength = dataLength; }
        public boolean isNullable() { return nullable; }
        public void setNullable(boolean nullable) { this.nullable = nullable; }
        public String getColumnComment() { return columnComment; }
        public void setColumnComment(String columnComment) { this.columnComment = columnComment; }
        public boolean isPrimaryKey() { return primaryKey; }
        public void setPrimaryKey(boolean primaryKey) { this.primaryKey = primaryKey; }
        public int getDataPrecision() { return dataPrecision; }
        public void setDataPrecision(int dataPrecision) { this.dataPrecision = dataPrecision; }
        public int getDataScale() { return dataScale; }
        public void setDataScale(int dataScale) { this.dataScale = dataScale; }
    }
}